package com.yuexin.project.dailymenu.everyday.export;

import com.alibaba.fastjson.JSON;
import com.yuexin.common.utils.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;

import static org.apache.poi.ss.usermodel.Cell.*;
import static org.apache.poi.ss.usermodel.CellType.NUMERIC;

public class ExcelReader {

    public static void main(String[] args) {



        List<ExportDayItem> exportDayItemList = ExcelReader.parse(ExcelReader.readExcel("E:\\mj-project\\meals_api\\target\\export\\download\\8a3a4048a588cc7a3cb70ab03f1336df.xls"));
        System.out.println(JSON.toJSONString(exportDayItemList));
        ExcelReader.writeExcel("早餐", exportDayItemList);
    }

    public   static  String parseTime(String time) {
        Date date = DateUtil.getJavaDate(Double.parseDouble(time));
        SimpleDateFormat ft = new SimpleDateFormat("yyyy-MM-dd");
        return ft.format(date);
    }


    public static List<ExportDayItem> parse(List<List<String>> dataList) {
        HashMap<String, List<ExportMenuItem>> map = new HashMap<String, List<ExportMenuItem>>();

        List<ExportDayItem> dayItems = new ArrayList<ExportDayItem>();
        int days=0;
        List<String> weekDataList= dataList.get(1);
         boolean startWeekIndex=false;
        for( String d : weekDataList) {
            if (!d.equals("")) {
                startWeekIndex = true;
            }
            if (startWeekIndex) {
                if (!d.equals("")) {
                    days++;
                } else {
                    startWeekIndex = false;
                }
            }
        }

        int index = 0;
        String lastType = "";
        Map<Integer, String> weekMap = new HashMap();

        for (List<String> list : dataList) {
            if (index == 0) {
                for (int i = 2; i < 2+days; i++) {
                    String week = list.get(i);
                    if (map.get(week) == null) {
                        map.put(week, new ArrayList<ExportMenuItem>());
                        weekMap.put(i, week);
                    }
                }
            } else if (index == 1  ) {
                index++;
                continue;
            } else if (index >= 2) {
                String type = list.get(1);
                if (!type.equals("")) {
                    lastType = list.get(1);
                }
                for (int i = 2; i < 2+days; i++) {
                    String week = weekMap.get(i);

                    parseType(map, week, lastType, list.get(i));

                }
            }

            if (list.get(0).equals("")) {
                break;
            }
            index++;
        }


        for (int i = 2; i < 2+days; i++) {

            String week = weekMap.get(i);
            ExportDayItem dayItem = new ExportDayItem();
            dayItem.setDay(week);
            dayItem.setWeek(getWeekOfDate(week));
            dayItem.setMenuItemList(map.get(week));
            dayItems.add(dayItem);
        }
        System.out.println(dayItems);
        return dayItems;

    }


    /**
     * 获取当前日期是星期几
     *
     * @return 当前日期是星期几
     */
    public static  String getWeekOfDate(String date) {
        String[] weekDays = {"星期日", "星期一", "星期二", "星期三", "星期四", "星期五", "星期六"};
        String dayOfweek = "-1";
        try {
            SimpleDateFormat myFormatter = new SimpleDateFormat("yyyy-MM-dd");
            Date dt = myFormatter.parse(date);
            Calendar cal = Calendar.getInstance();
            cal.setTime(dt);

            int w = cal.get(Calendar.DAY_OF_WEEK) - 1;
            if (w < 0)
                w = 0;
            return weekDays[w];
        } catch (Exception ex) {
            return "";
        }
    }

    private  static  void parseType(HashMap<String, List<ExportMenuItem>> map, String week, String type, String menuName) {
        List<ExportMenuItem> menuItemList = map.get(week);
        ExportMenuItem item = null;
        for (ExportMenuItem i : menuItemList) {
            if (i.getType().equals(type)) {
                item = i;
                break;
            }
        }

        if (item == null) {
            item = new ExportMenuItem();
            item.setType(type);

            item.setTypeList(new ArrayList<String>());
            map.get(week).add(item);

        }
        if (!menuName.equals("")) {
            item.getTypeList().add(menuName);
        }
    }


    /**
     * 读取excel
     *
     * @param filePath excel路径
     * @return
     */
    public static List<List<String>> readExcel(String filePath) {
        Sheet sheet = null;
        Row row = null;

        List<List<String>> list = null;
        String cellData = null;
        Workbook wb = null;
        if (filePath == null) {
            return null;
        }
        String extString = filePath.substring(filePath.lastIndexOf("."));
        InputStream is = null;
        try {
            is = new FileInputStream(filePath);
            if (".xls".equals(extString)) {
                wb = new HSSFWorkbook(is);
            } else if (".xlsx".equals(extString)) {
                try {
                    wb = new XSSFWorkbook(is);
                } catch (IOException e) {
                    e.printStackTrace();
                }
            } else {
                wb = null;
            }
            if (wb != null) {
                // 用来存放表中数据
                list = new ArrayList<List<String>>();
                // 获取第一个sheet
                sheet = wb.getSheetAt(0);
                // 获取最大行数
                int rownum = sheet.getPhysicalNumberOfRows();
                // 获取第一行

                row = sheet.getRow(1);
                // 获取最大列数
                int colnum = row.getPhysicalNumberOfCells();
                for (int i = 1; i < rownum; i++) {

                    row = sheet.getRow(i);

                    if (row != null) {
                        List<String> item = new ArrayList<String>();
                        for (int j = 0; j < colnum; j++) {

                            cellData = (String) getXssfCellValue(row.getCell(j));
                            item.add(cellData);
                        }
                        list.add(item);
                    }


                }
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

        return list;
    }


    private static String getXssfCellValue(Cell xssfCell) {
        String cellvalue = "";
        DataFormatter formatter = new DataFormatter();
        if (null != xssfCell) {
            switch (xssfCell.getCellTypeEnum()) {
                case NUMERIC: // 数字
                    if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(xssfCell)) {
                        cellvalue = formatter.formatCellValue(xssfCell);
                    } else {
                        double value = xssfCell.getNumericCellValue();
                        int intValue = (int) value;
                        cellvalue = value - intValue == 0 ? String.valueOf(intValue) : String.valueOf(value);
                    }
                    break;
                case  STRING: // 字符串
                    cellvalue = xssfCell.getStringCellValue();
                    break;
                case  BOOLEAN: // Boolean
                    cellvalue = String.valueOf(xssfCell.getBooleanCellValue());
                    break;
                case  FORMULA: // 公式
                    cellvalue = String.valueOf(xssfCell.getCellFormula());
                    break;
                case  BLANK: // 空值
                    cellvalue = "";
                    break;
                case  ERROR: // 故障
                    cellvalue = "";
                    break;
                default:
                    cellvalue = "UNKNOWN TYPE";
                    break;
            }
        }
        return cellvalue.trim();
    }


    private  static  HSSFCellStyle getStyle(HSSFWorkbook wb, int width) {
        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中

        style.setBorderBottom(BorderStyle.THIN); //下边框
        style.setBorderLeft(BorderStyle.THIN);//左边框
        style.setBorderTop(BorderStyle.THIN);//上边框
        style.setBorderRight(BorderStyle.THIN);//右边框

        // 自动换行
        style.setWrapText(true);

        // 生成一个字体
        HSSFFont font = wb.createFont();
        font.setFontHeightInPoints((short) width);
        font.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex() );
        font.setBold(true);
        // 把字体 应用到当前样式
        style.setFont(font);
        return style;
    }

    public static HSSFWorkbook  writeExcel(String sheetName, List<ExportDayItem> exportDayItems) {


        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件

        HSSFWorkbook wb = new HSSFWorkbook();
        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow firstRow = sheet.createRow(0);

        HSSFCellStyle style = getStyle(wb, 15);

        HSSFCell firstCell = firstRow.createCell(0);
        firstCell.setCellStyle(getStyle(wb, 23));

        HSSFRow rowOne = sheet.createRow(1);
        HSSFRow rowTwo = sheet.createRow(2);

        HashMap<String, Integer> typeMap = getTypeMap(exportDayItems);

        String firstDay = "";
        String lastDay = "";


        ArrayList<String> typeList = new ArrayList<String>();
        HashMap<String, List<String>> menuList = new HashMap<String, List<String>>();
        List<String> weekList = new ArrayList<String>();
        List<String> weekNameList = new ArrayList<String>();
        for (int i = 0; i < exportDayItems.size(); i++) {
            String key = exportDayItems.get(i).getDay();

            if (i == 0) {
                firstDay = key;
            } else if (i == exportDayItems.size() - 1) {
                lastDay = key;
            }
            weekList.add(key);
            weekNameList.add(exportDayItems.get(i).getWeek());
            List<ExportMenuItem> menuItemList = exportDayItems.get(i).getMenuItemList();
            for (ExportMenuItem menuItem : menuItemList) {
                String type = menuItem.getType();
                if (!typeList.contains(type)) {
                    typeList.add(type);
                }
                menuList.put(type + "_" + i, menuItem.getTypeList());
            }
        }

        {

            HSSFCell numCell = rowOne.createCell(0);
            numCell.setCellStyle(style);
            numCell.setCellValue("序号");

        }
        {
            HSSFCell typeCell = rowOne.createCell(1);
            typeCell.setCellStyle(style);
            typeCell.setCellValue("品类");

            for (int i = 0; i < weekList.size(); i++) {
                HSSFCell cell = rowOne.createCell(2 + i);
                cell.setCellStyle(style);
                cell.setCellValue(weekList.get(i));
            }
        }

        {
            CellRangeAddress region = new CellRangeAddress(1, 2, 1, 1);
            sheet.addMergedRegion(region);

        }

        for (int i = 0; i < weekList.size(); i++) {
            HSSFCell cell = rowTwo.createCell(2 + i);
            cell.setCellStyle(style);
            cell.setCellValue(String.valueOf(weekNameList.get(i)));

        }

        {
            CellRangeAddress region = new CellRangeAddress(1, 2, 0, 0);
            sheet.addMergedRegion(region);

        }


        int index = 1;
        int rowIndex = 3;
        int allDays = exportDayItems.size();

        HashMap<Integer, HSSFRow> rowHashMap = new HashMap<Integer, HSSFRow>();
        for (int i = 0; i < typeList.size(); i++) {
            String type = typeList.get(i);

            int max = typeMap.get(type);

            for (int j = rowIndex; j < rowIndex + max; j++) {
                // System.out.println("j="+j);
                HSSFRow hssfRow = rowHashMap.get(j);
                if (hssfRow == null) {
                    hssfRow = sheet.createRow(j);
                    rowHashMap.put(j, hssfRow);
                }

                HSSFCell indexCell = hssfRow.createCell(0);
                indexCell.setCellStyle(style);
                indexCell.setCellValue(index);

                HSSFCell TypeCell = hssfRow.createCell(1);
                TypeCell.setCellValue(type);
                TypeCell.setCellStyle(style);
                index++;
                //System.out.println( index +"  --"+type);
            }

            for (int j = 0; j <= allDays - 1; j++) {
                String key = type + "_" + j;
                List<String> menuItems = menuList.get(key);
                setCellByDay(rowIndex, j, style, rowHashMap, menuItems);

            }

            int nextRow = rowIndex + max - 1;
            if (nextRow > rowIndex) {
                CellRangeAddress region = new CellRangeAddress(rowIndex, nextRow, 1, 1);
                sheet.addMergedRegion(region);
            }

            rowIndex += max;

        }


        firstCell.setCellValue(firstDay + "至" + lastDay  + sheetName + "菜谱");
        firstRow.setHeightInPoints(50);


        CellRangeAddress region = new CellRangeAddress(0, 0, 0, 6);
        sheet.addMergedRegion(region);

        for (int i = 2; i <= (2 + allDays - 1); i++) {
            sheet.setColumnWidth(i, 30 * 256);
        }

        return wb;
    }


    private static void   setCellByDay(int rowIndex, int dayIndex, HSSFCellStyle style,
                              HashMap<Integer, HSSFRow>
                                      rowHashMap, List<String> menuItemList) {


        int j = rowIndex;
        int rowNexIndex = 0;
        for (String item : menuItemList) {
            //System.out.println("xj=" + (j + rowNexIndex));
            HSSFRow hssfRow = rowHashMap.get(j + rowNexIndex);
            HSSFCell indexCell = hssfRow.createCell(dayIndex + 2);
            indexCell.setCellStyle(style);
            indexCell.setCellValue(item);
            //System.out.println("---->" +  item);
            rowNexIndex++;
        }
    }

    /**
     * @param menuMap
     * @return
     */
    private static HashMap<String, Integer> getTypeMap(List<ExportDayItem> menuMap) {

        HashMap<String, Integer> typeCountMap = new HashMap<String, Integer>();
        for (ExportDayItem dayItem : menuMap) {
            String key = dayItem.getDay();
            List<ExportMenuItem> menuItemList = dayItem.getMenuItemList();
            for (ExportMenuItem menuItem : menuItemList) {
                String type = menuItem.getType();
                if (typeCountMap.get(type) == null) {
                    typeCountMap.put(type, menuItem.getTypeList().size());
                }
                int size = typeCountMap.get(type);
                int maxSize = menuItem.getTypeList().size();
                if (size < maxSize) {
                    typeCountMap.put(type, menuItemList.size());
                }
            }
        }
        return typeCountMap;
    }

    /**
     * @param today
     * @return
     */

    public  static  int getWeekOfYear(String today) {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        Date date = null;
        try {
            date = format.parse(today);
        } catch (Exception e) {
            e.printStackTrace();
        }
        Calendar calendar = Calendar.getInstance();
        calendar.setFirstDayOfWeek(Calendar.MONDAY);
        calendar.setTime(date);
        return calendar.get(Calendar.WEEK_OF_YEAR);
    }


    /**
     * @param today
     * @return
     */

    public  static  int getYear(String today) {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        Date date = null;
        try {
            date = format.parse(today);
        } catch (Exception e) {
            e.printStackTrace();
        }
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        return calendar.get(Calendar.YEAR);
    }



    /**
     * @param today
     * @return
     */

    public  static  Date  parseDay(String today) {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        Date date = null;
        try {
            date = format.parse(today);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return date;
    }

    public static void setFileName(String fileName, HttpServletResponse response) {
        if (StringUtils.isEmpty(fileName)) {
            fileName = "export.xls";
        } else {
            try {
                fileName = URLEncoder.encode(fileName, "UTF-8");
            } catch (UnsupportedEncodingException var3) {
                fileName = "export.xls";
            }
        }

        response.setHeader("Content-disposition", "attachment; filename=" + fileName);
        response.setContentType("application/msexcel;charset=UTF-8");
        response.setHeader("Pragma", "No-cache");
        response.setHeader("Cache-Control", "no-cache");
        response.setDateHeader("Expires", 0L);
    }


}